import json
import re
from flask import current_app
from traceback import format_exc
from app.models.fields_conf import json_fields, int_fields, date_fields
from app.utils.mysql_pool import MysqlPool
from config import OPRRATORS_CONVERT


def get_sql(params, table, operation, condition=None):
    """
    获取单表操作sql语句
    :param params: 表结构数据,dist格式
    :param table:  表名
    :param operation: 操作方式: update, insert, delete
    :param condition: 当操作方式为update或delete 才有该参数
            {
                "name":{"like": "zhang%"},
                "created_at": {
                    "gt": "2016-12-16 16:42:23",
                    "lt": "2016-12-16 16:42:23"
                }
            }
    :return: 返回完整sql语句, sql语句参数
    """
    # 定义sql语句参数
    ls = []

    # 更新sql语句
    if operation == 'update':
        # 组织字段,获取键值
        kv_list = []
        for i in params:
            value = params[i]
            if i in json_fields:
                value = json.dumps(value, ensure_ascii=False)
            kv = str(i) + '=%s'
            kv_list.append(kv)
            ls.append(value)

        # 获取条件
        w_list = []
        for key in condition:
            dct = condition[key]
            for operator in dct:
                where_value = dct[operator]
                if 'like' in operator:
                    where_value = re.sub(r'%', '%%', dct[operator])
                if key not in int_fields and key not in json_fields:
                    where_value = '"' + where_value + '"'
                s = [key, OPRRATORS_CONVERT[operator], str(where_value)]
                w_list.append(' '.join(s))

        # 拼接sql
        kv_content = ','.join(kv_list)
        w_content = ' and '.join(w_list)
        sql = 'update ' + table + ' set ' + kv_content + ' where ' + w_content

    # 插入sql语句
    elif operation == 'insert':
        # 组织字段
        k_list = []
        for k in params.keys():
            value = params[k]
            if k in json_fields:
                value = json.dumps(value, ensure_ascii=False)
            k_list.append(k)
            ls.append(value)

        # 拼接sql
        k_content = ','.join(k_list)
        sql = 'insert into ' + table + ' (' + k_content + ') values %s'
        ls = [tuple(ls)]

    # 删除sql语句
    elif operation == 'delete':
        # 获取条件
        w_list = []
        for key in condition:
            dct = condition[key]
            for operator in dct:
                where_value = dct[operator]
                if 'like' in operator:
                    where_value = re.sub(r'%', '%%', dct[operator])
                if key not in int_fields and key not in json_fields:
                    where_value = '"' + where_value + '"'
                s = [key, OPRRATORS_CONVERT[operator], str(where_value)]
                w_list.append(' '.join(s))

        # 拼接sql
        w_content = ' and '.join(w_list)
        sql = "delete from " + table + " where " + w_content

    else:
        raise Exception('method error')

    return sql, ls


def save(params, table, operation, condition=None):
    """
    针对一张表的增删改操作
    """
    # 获取sql语句
    sql, ls = get_sql(params, table, operation, condition=condition)
    # 连接数据库
    db = MysqlPool()
    db.conn.begin()
    try:
        print('save:%s' % sql)
        print('save_params: %s' % ls)
        db.cursor.execute(sql, ls)
        db.conn.commit()
        db.dispose()
        return True
    except:
        current_app.logger.error(format_exc())
        db.conn.rollback()
        db.dispose()
        return False


def search(query, table):
    """
    针对一张表的查询数据
    :param query= {
                    "offset":0,
                    "limit":20,
                    "sort":{
                        "id": "asc",
                        "name": "desc"
                     },
                    "fields":["id","name","created_at"],
                    "where":{
                        "name":{"like": "zhang%"},
                        "created_at": {
                            "gt": "2016-12-16 16:42:23",
                            "lt": "2016-12-16 16:42:23"
                        }
                    }
                }
    :return {
                "data":[],
                "total": 21
            }

    """
    result = {}

    sql = 'select * from ' + table + ' where deleted_at="0000-01-01 00:00:00"'
    sql_count = 'select count(*) as total from ' + table + ' where deleted_at="0000-01-01 00:00:00"'

    if query:

        # 查询字段
        if 'fields' in query:
            fields_list = query['fields']
            if fields_list:
                field_content = ','.join(fields_list)
                sql = 'select ' + field_content + ' from '+ table + ' where deleted_at="0000-01-01 00:00:00"'

        # 查询条件
        if 'where' in query:
            target_wheres = []
            wheres = query['where']
            for field in wheres:
                dct = wheres[field]
                for operator in dct:
                    where_value = dct[operator]
                    if 'like' in operator:
                        where_value = re.sub(r'%', '%%', dct[operator])
                    if field not in int_fields and field not in json_fields:
                        where_value = '"' + where_value + '"'
                    s = [field, OPRRATORS_CONVERT[operator], str(where_value)]
                    target_wheres.append(' '.join(s))
            # 拼接sql
            if target_wheres:
                l = ' and '.join(target_wheres)
                sql = sql + ' and ' + l
                sql_count = sql_count + ' and ' + l

        # 查询排序
        if 'sort' in query:
            target_sorts = []
            sorts = query['sort']
            for field in sorts:
                dct = sorts[field].lower()
                s = [field, dct]
                target_sorts.append(' '.join(s))
            # 拼接sql
            if target_sorts:
                l = ','.join(target_sorts)
                sql = sql + ' order by ' + l

        # 查询分页
        if 'limit' in query:
            limit = str(query['limit'])
            if 'offset' in query:
                offset = str(query['offset'])
            else:
                offset = '0'
            sql = sql + ' limit ' + offset + ',' + limit

    # 连接数据库
    print('search:%s' % sql)
    print('search count: %s' % sql_count)
    db = MysqlPool()
    data = db.get_all(sql)
    count = db.get_one(sql_count)
    db.dispose()

    # 字段格式
    for row in data:
        for field in row:
            if row[field] is None:
                row[field] = ''
            if field in date_fields:
                row[field] = str(row[field])
            if field in json_fields:
                if row[field]:
                    row[field] = json.loads(row[field])

    # 组织返回结果
    result['data'] = data
    result['total'] = count['total']
    return result


if __name__ == '__main__':
    params = {"name":"xiaomi", "addr":{"ash":12,"d":"we"}, "iphone":"12121212", "num":1}
    table = 'test'
    operation = 'update'
    condition = {"id":{"eq":1}, "num":{"eq":3}}
    sql, ls = get_sql(params, table, operation, condition=condition)
    print(sql)
    print(ls)
    # save(params, table, operation, condition=condition)

    # query = {
    #     "offset":0,
    #     "limit":20,
    #     "sort":{
    #         "id": "asc",
    #         "name": "desc"
    #      },
    #     "fields":["id","name","created_at"],
    #     "where":{
    #         "name":{"like": "zhang%"},
    #         "created_at": {
    #             "gt": "2016-12-16 16:42:23",
    #             "lt": "2016-12-16 16:42:23"
    #         }
    #     }
    # }
    # search(query, "test")
